﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using V5_Model;

namespace V5_DAL
{
    public class DALUser
    {
        #region  成员方法

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "K_User");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from K_User");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ModelUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into K_User(");
            strSql.Append("ID,UserName,UserPassword,RegDate,UserScore,ScoreUsed,UserEmail,UserSex,LoginDate,LoginTimes,IdentityCard,PostCode,AreaID,Address3,Address2,Address,IsLock,GroupID,Question,Answer,LoginIP,FrozenDeposit,FrozenScore,BirthDay,QQ,Telephone,Phone,TrueName,CommendUser,NewPassWord,GUID,Readme)");
            strSql.Append(" values (");
            strSql.Append("@ID,@UserName,@UserPassword,@RegDate,@UserScore,@ScoreUsed,@UserEmail,@UserSex,@LoginDate,@LoginTimes,@IdentityCard,@PostCode,@AreaID,@Address3,@Address2,@Address,@IsLock,@GroupID,@Question,@Answer,@LoginIP,@FrozenDeposit,@FrozenScore,@BirthDay,@QQ,@Telephone,@Phone,@TrueName,@CommendUser,@NewPassWord,@GUID,@Readme)");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
					new SqlParameter("@UserPassword", SqlDbType.NVarChar,50),
					new SqlParameter("@RegDate", SqlDbType.DateTime),
					new SqlParameter("@UserScore", SqlDbType.Int,4),
					new SqlParameter("@ScoreUsed", SqlDbType.Int,4),
					new SqlParameter("@UserEmail", SqlDbType.NVarChar,50),
					new SqlParameter("@UserSex", SqlDbType.Int,4),
					new SqlParameter("@LoginDate", SqlDbType.DateTime),
					new SqlParameter("@LoginTimes", SqlDbType.Int,4),
					new SqlParameter("@IdentityCard", SqlDbType.NVarChar,50),
					new SqlParameter("@PostCode", SqlDbType.Int,4),
					new SqlParameter("@AreaID", SqlDbType.Int,4),
					new SqlParameter("@Address3", SqlDbType.NVarChar,50),
					new SqlParameter("@Address2", SqlDbType.NVarChar,50),
					new SqlParameter("@Address", SqlDbType.NVarChar,150),
					new SqlParameter("@IsLock", SqlDbType.Int,4),
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@Question", SqlDbType.NVarChar,50),
					new SqlParameter("@Answer", SqlDbType.NVarChar,50),
					new SqlParameter("@LoginIP", SqlDbType.NVarChar,50),
					new SqlParameter("@FrozenDeposit", SqlDbType.Decimal,9),
					new SqlParameter("@FrozenScore", SqlDbType.Int,4),
					new SqlParameter("@BirthDay", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.NVarChar,50),
					new SqlParameter("@Telephone", SqlDbType.NVarChar,50),
					new SqlParameter("@Phone", SqlDbType.NVarChar,50),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@CommendUser", SqlDbType.NVarChar,50),
					new SqlParameter("@NewPassWord", SqlDbType.NVarChar,50),
					new SqlParameter("@GUID", SqlDbType.NVarChar,50),
					new SqlParameter("@Readme", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.UserName;
            parameters[2].Value = model.UserPassword;
            parameters[3].Value = model.RegDate;
            parameters[4].Value = model.UserScore;
            parameters[5].Value = model.ScoreUsed;
            parameters[6].Value = model.UserEmail;
            parameters[7].Value = model.UserSex;
            parameters[8].Value = model.LoginDate;
            parameters[9].Value = model.LoginTimes;
            parameters[10].Value = model.IdentityCard;
            parameters[11].Value = model.PostCode;
            parameters[12].Value = model.AreaID;
            parameters[13].Value = model.Address3;
            parameters[14].Value = model.Address2;
            parameters[15].Value = model.Address;
            parameters[16].Value = model.IsLock;
            parameters[17].Value = model.GroupID;
            parameters[18].Value = model.Question;
            parameters[19].Value = model.Answer;
            parameters[20].Value = model.LoginIP;
            parameters[21].Value = model.FrozenDeposit;
            parameters[22].Value = model.FrozenScore;
            parameters[23].Value = model.BirthDay;
            parameters[24].Value = model.QQ;
            parameters[25].Value = model.Telephone;
            parameters[26].Value = model.Phone;
            parameters[27].Value = model.TrueName;
            parameters[28].Value = model.CommendUser;
            parameters[29].Value = model.NewPassWord;
            parameters[30].Value = model.GUID;
            parameters[31].Value = model.Readme;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(ModelUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update K_User set ");
            strSql.Append("UserName=@UserName,");
            strSql.Append("UserPassword=@UserPassword,");
            strSql.Append("RegDate=@RegDate,");
            strSql.Append("UserScore=@UserScore,");
            strSql.Append("ScoreUsed=@ScoreUsed,");
            strSql.Append("UserEmail=@UserEmail,");
            strSql.Append("UserSex=@UserSex,");
            strSql.Append("LoginDate=@LoginDate,");
            strSql.Append("LoginTimes=@LoginTimes,");
            strSql.Append("IdentityCard=@IdentityCard,");
            strSql.Append("PostCode=@PostCode,");
            strSql.Append("AreaID=@AreaID,");
            strSql.Append("Address3=@Address3,");
            strSql.Append("Address2=@Address2,");
            strSql.Append("Address=@Address,");
            strSql.Append("IsLock=@IsLock,");
            strSql.Append("GroupID=@GroupID,");
            strSql.Append("Question=@Question,");
            strSql.Append("Answer=@Answer,");
            strSql.Append("LoginIP=@LoginIP,");
            strSql.Append("FrozenDeposit=@FrozenDeposit,");
            strSql.Append("FrozenScore=@FrozenScore,");
            strSql.Append("BirthDay=@BirthDay,");
            strSql.Append("QQ=@QQ,");
            strSql.Append("Telephone=@Telephone,");
            strSql.Append("Phone=@Phone,");
            strSql.Append("TrueName=@TrueName,");
            strSql.Append("CommendUser=@CommendUser,");
            strSql.Append("NewPassWord=@NewPassWord,");
            strSql.Append("GUID=@GUID,");
            strSql.Append("Readme=@Readme");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
					new SqlParameter("@UserPassword", SqlDbType.NVarChar,50),
					new SqlParameter("@RegDate", SqlDbType.DateTime),
					new SqlParameter("@UserScore", SqlDbType.Int,4),
					new SqlParameter("@ScoreUsed", SqlDbType.Int,4),
					new SqlParameter("@UserEmail", SqlDbType.NVarChar,50),
					new SqlParameter("@UserSex", SqlDbType.Int,4),
					new SqlParameter("@LoginDate", SqlDbType.DateTime),
					new SqlParameter("@LoginTimes", SqlDbType.Int,4),
					new SqlParameter("@IdentityCard", SqlDbType.NVarChar,50),
					new SqlParameter("@PostCode", SqlDbType.Int,4),
					new SqlParameter("@AreaID", SqlDbType.Int,4),
					new SqlParameter("@Address3", SqlDbType.NVarChar,50),
					new SqlParameter("@Address2", SqlDbType.NVarChar,50),
					new SqlParameter("@Address", SqlDbType.NVarChar,150),
					new SqlParameter("@IsLock", SqlDbType.Int,4),
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@Question", SqlDbType.NVarChar,50),
					new SqlParameter("@Answer", SqlDbType.NVarChar,50),
					new SqlParameter("@LoginIP", SqlDbType.NVarChar,50),
					new SqlParameter("@FrozenDeposit", SqlDbType.Decimal,9),
					new SqlParameter("@FrozenScore", SqlDbType.Int,4),
					new SqlParameter("@BirthDay", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.NVarChar,50),
					new SqlParameter("@Telephone", SqlDbType.NVarChar,50),
					new SqlParameter("@Phone", SqlDbType.NVarChar,50),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@CommendUser", SqlDbType.NVarChar,50),
					new SqlParameter("@NewPassWord", SqlDbType.NVarChar,50),
					new SqlParameter("@GUID", SqlDbType.NVarChar,50),
					new SqlParameter("@Readme", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.UserName;
            parameters[2].Value = model.UserPassword;
            parameters[3].Value = model.RegDate;
            parameters[4].Value = model.UserScore;
            parameters[5].Value = model.ScoreUsed;
            parameters[6].Value = model.UserEmail;
            parameters[7].Value = model.UserSex;
            parameters[8].Value = model.LoginDate;
            parameters[9].Value = model.LoginTimes;
            parameters[10].Value = model.IdentityCard;
            parameters[11].Value = model.PostCode;
            parameters[12].Value = model.AreaID;
            parameters[13].Value = model.Address3;
            parameters[14].Value = model.Address2;
            parameters[15].Value = model.Address;
            parameters[16].Value = model.IsLock;
            parameters[17].Value = model.GroupID;
            parameters[18].Value = model.Question;
            parameters[19].Value = model.Answer;
            parameters[20].Value = model.LoginIP;
            parameters[21].Value = model.FrozenDeposit;
            parameters[22].Value = model.FrozenScore;
            parameters[23].Value = model.BirthDay;
            parameters[24].Value = model.QQ;
            parameters[25].Value = model.Telephone;
            parameters[26].Value = model.Phone;
            parameters[27].Value = model.TrueName;
            parameters[28].Value = model.CommendUser;
            parameters[29].Value = model.NewPassWord;
            parameters[30].Value = model.GUID;
            parameters[31].Value = model.Readme;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from K_User ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ModelUser GetModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,UserName,UserPassword,RegDate,UserScore,ScoreUsed,UserEmail,UserSex,LoginDate,LoginTimes,IdentityCard,PostCode,AreaID,Address3,Address2,Address,IsLock,GroupID,Question,Answer,LoginIP,FrozenDeposit,FrozenScore,BirthDay,QQ,Telephone,Phone,TrueName,CommendUser,NewPassWord,GUID,Readme from K_User ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            ModelUser model = new ModelUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                model.UserPassword = ds.Tables[0].Rows[0]["UserPassword"].ToString();
                if (ds.Tables[0].Rows[0]["RegDate"].ToString() != "")
                {
                    model.RegDate = DateTime.Parse(ds.Tables[0].Rows[0]["RegDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserScore"].ToString() != "")
                {
                    model.UserScore = int.Parse(ds.Tables[0].Rows[0]["UserScore"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ScoreUsed"].ToString() != "")
                {
                    model.ScoreUsed = int.Parse(ds.Tables[0].Rows[0]["ScoreUsed"].ToString());
                }
                model.UserEmail = ds.Tables[0].Rows[0]["UserEmail"].ToString();
                if (ds.Tables[0].Rows[0]["UserSex"].ToString() != "")
                {
                    model.UserSex = int.Parse(ds.Tables[0].Rows[0]["UserSex"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginDate"].ToString() != "")
                {
                    model.LoginDate = DateTime.Parse(ds.Tables[0].Rows[0]["LoginDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginTimes"].ToString() != "")
                {
                    model.LoginTimes = int.Parse(ds.Tables[0].Rows[0]["LoginTimes"].ToString());
                }
                model.IdentityCard = ds.Tables[0].Rows[0]["IdentityCard"].ToString();
                if (ds.Tables[0].Rows[0]["PostCode"].ToString() != "")
                {
                    model.PostCode = int.Parse(ds.Tables[0].Rows[0]["PostCode"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AreaID"].ToString() != "")
                {
                    model.AreaID = int.Parse(ds.Tables[0].Rows[0]["AreaID"].ToString());
                }
                model.Address3 = ds.Tables[0].Rows[0]["Address3"].ToString();
                model.Address2 = ds.Tables[0].Rows[0]["Address2"].ToString();
                model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                if (ds.Tables[0].Rows[0]["IsLock"].ToString() != "")
                {
                    model.IsLock = int.Parse(ds.Tables[0].Rows[0]["IsLock"].ToString());
                }
                if (ds.Tables[0].Rows[0]["GroupID"].ToString() != "")
                {
                    model.GroupID = int.Parse(ds.Tables[0].Rows[0]["GroupID"].ToString());
                }
                model.Question = ds.Tables[0].Rows[0]["Question"].ToString();
                model.Answer = ds.Tables[0].Rows[0]["Answer"].ToString();
                model.LoginIP = ds.Tables[0].Rows[0]["LoginIP"].ToString();
                if (ds.Tables[0].Rows[0]["FrozenDeposit"].ToString() != "")
                {
                    model.FrozenDeposit = decimal.Parse(ds.Tables[0].Rows[0]["FrozenDeposit"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FrozenScore"].ToString() != "")
                {
                    model.FrozenScore = int.Parse(ds.Tables[0].Rows[0]["FrozenScore"].ToString());
                }
                model.BirthDay = ds.Tables[0].Rows[0]["BirthDay"].ToString();
                model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                model.Telephone = ds.Tables[0].Rows[0]["Telephone"].ToString();
                model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                model.TrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                model.CommendUser = ds.Tables[0].Rows[0]["CommendUser"].ToString();
                model.NewPassWord = ds.Tables[0].Rows[0]["NewPassWord"].ToString();
                model.GUID = ds.Tables[0].Rows[0]["GUID"].ToString();
                model.Readme = ds.Tables[0].Rows[0]["Readme"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,UserName,UserPassword,RegDate,UserScore,ScoreUsed,UserEmail,UserSex,LoginDate,LoginTimes,IdentityCard,PostCode,AreaID,Address3,Address2,Address,IsLock,GroupID,Question,Answer,LoginIP,FrozenDeposit,FrozenScore,BirthDay,QQ,Telephone,Phone,TrueName,CommendUser,NewPassWord,GUID,Readme ");
            strSql.Append(" FROM K_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,UserName,UserPassword,RegDate,UserScore,ScoreUsed,UserEmail,UserSex,LoginDate,LoginTimes,IdentityCard,PostCode,AreaID,Address3,Address2,Address,IsLock,GroupID,Question,Answer,LoginIP,FrozenDeposit,FrozenScore,BirthDay,QQ,Telephone,Phone,TrueName,CommendUser,NewPassWord,GUID,Readme ");
            strSql.Append(" FROM K_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "K_User";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 1;
            parameters[6].Value = strWhere;
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
        }

        #endregion  成员方法
    }
}
